﻿Imports System
Imports System.IO

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration

Public Class ClsSeguridad
    Dim dg_conexion As String = ConfigurationManager.ConnectionStrings("conexion").ConnectionString
    Dim timesql_servidor As Integer = CType(ConfigurationManager.AppSettings("timesql_servidor"), Integer)

    Public Function busca_usuario_autenticacion(ByVal dc_usuario As String, ByRef dg_usuario As String, ByRef dg_password As String, ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_b_workflow_usuario_autenticacion"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_usuario", SqlDbType.VarChar, 20)
            cmd.Parameters("@dc_usuario").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_usuario").Value = dc_usuario

            cmd.Parameters.Add("@dg_password", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_password").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_password").Value = ""

            cmd.Parameters.Add("@dg_usuario", SqlDbType.VarChar, 50)
            cmd.Parameters("@dg_usuario").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_usuario").Value = ""

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            busca_usuario_autenticacion = cmd.Parameters("ReturnValue").Value

            If busca_usuario_autenticacion = 0 Then
                dg_usuario = cmd.Parameters("@dg_usuario").Value
                dg_password = cmd.Parameters("@dg_password").Value
            Else
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function BuscarUsuario(ByVal dc_usuario As String, _
                                  ByRef dg_usuario As String, _
                                  ByRef dg_password As String, _
                                  ByRef dg_mail As String, _
                                  ByRef dm_vigente As String, _
                                  ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_b_workflow_usuario"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_usuario", SqlDbType.VarChar, 20)
            cmd.Parameters("@dc_usuario").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_usuario").Value = dc_usuario

            cmd.Parameters.Add("@dg_usuario", SqlDbType.VarChar, 50)
            cmd.Parameters("@dg_usuario").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_usuario").Value = ""

            cmd.Parameters.Add("@dg_password", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_password").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_password").Value = ""

            cmd.Parameters.Add("@dg_mail", SqlDbType.VarChar, 80)
            cmd.Parameters("@dg_mail").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_mail").Value = ""

            cmd.Parameters.Add("@dm_vigente", SqlDbType.Char, 1)
            cmd.Parameters("@dm_vigente").Direction = ParameterDirection.Output
            cmd.Parameters("@dm_vigente").Value = ""

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            BuscarUsuario = cmd.Parameters("ReturnValue").Value

            If BuscarUsuario = 0 Then
                dg_usuario = cmd.Parameters("@dg_usuario").Value
                dg_password = cmd.Parameters("@dg_password").Value
                dg_mail = cmd.Parameters("@dg_mail").Value
                dm_vigente = cmd.Parameters("@dm_vigente").Value
            Else
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function ModificarUsuario(ByVal dc_usuario As String, _
                              ByVal dg_usuario As String, _
                              ByVal dg_password As String, _
                              ByVal dg_mail As String, _
                              ByVal dm_vigente As String, _
                              ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_u_workflow_usuario"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_usuario", SqlDbType.VarChar, 20)
            cmd.Parameters("@dc_usuario").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_usuario").Value = dc_usuario

            cmd.Parameters.Add("@dg_usuario", SqlDbType.VarChar, 50)
            cmd.Parameters("@dg_usuario").Direction = ParameterDirection.Input
            cmd.Parameters("@dg_usuario").Value = dg_usuario

            cmd.Parameters.Add("@dg_password", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_password").Direction = ParameterDirection.Input
            cmd.Parameters("@dg_password").Value = dg_password

            cmd.Parameters.Add("@dg_mail", SqlDbType.VarChar, 80)
            cmd.Parameters("@dg_mail").Direction = ParameterDirection.Input
            cmd.Parameters("@dg_mail").Value = dg_mail

            cmd.Parameters.Add("@dm_vigente", SqlDbType.Char, 1)
            cmd.Parameters("@dm_vigente").Direction = ParameterDirection.Input
            cmd.Parameters("@dm_vigente").Value = dm_vigente

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            ModificarUsuario = cmd.Parameters("ReturnValue").Value

            If ModificarUsuario <> 0 Then
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function ObtieneUltimoPerfil(ByRef dc_perfil As Integer, ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_b_workflow_ultimo_perfil"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_perfil", SqlDbType.Int)
            cmd.Parameters("@dc_perfil").Direction = ParameterDirection.Output
            cmd.Parameters("@dc_perfil").Value = 0

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            ObtieneUltimoPerfil = cmd.Parameters("ReturnValue").Value

            If ObtieneUltimoPerfil = 0 Then
                dc_perfil = cmd.Parameters("@dc_perfil").Value
            Else
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function ConsultaTransacciones(ByRef obj_ds As DataSet, ByRef dg_resultado As String) As Long
        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_c_workflow_transaccion"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            Dim obj_da As SqlDataAdapter = Nothing

            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""
            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()

            obj_da = New SqlDataAdapter(cmd)
            obj_ds = New DataSet()
            obj_da.Fill(obj_ds)

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function busca_perfil(ByVal dc_perfil As Integer, ByRef dg_perfil As String, ByRef dm_vigente As String, ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_b_workflow_perfil"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_perfil", SqlDbType.Int)
            cmd.Parameters("@dc_perfil").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_perfil").Value = dc_perfil

            cmd.Parameters.Add("@dg_perfil", SqlDbType.VarChar, 80)
            cmd.Parameters("@dg_perfil").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_perfil").Value = ""

            cmd.Parameters.Add("@dm_vigente", SqlDbType.Char, 1)
            cmd.Parameters("@dm_vigente").Direction = ParameterDirection.Output
            cmd.Parameters("@dm_vigente").Value = ""

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            busca_perfil = cmd.Parameters("ReturnValue").Value

            If busca_perfil = 0 Then
                dg_perfil = cmd.Parameters("@dg_perfil").Value
                dm_vigente = cmd.Parameters("@dm_vigente").Value
            Else
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function
    Public Function ConsultaTransacciones_perfil(ByVal dc_perfil As Integer, ByRef obj_ds As DataSet, ByRef dg_resultado As String) As Long
        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_c_workflow_perfil_transaccion"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            Dim obj_da As SqlDataAdapter = Nothing

            cmd.Parameters.Add("@dc_perfil", SqlDbType.Int)
            cmd.Parameters("@dc_perfil").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_perfil").Value = dc_perfil

            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""
            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()

            obj_da = New SqlDataAdapter(cmd)
            obj_ds = New DataSet()
            obj_da.Fill(obj_ds)

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function Insertar_perfil(ByVal dc_perfil As Integer, ByVal dg_perfil As String, ByVal dm_vigente As String, ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_i_workflow_perfil"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_perfil", SqlDbType.Int)
            cmd.Parameters("@dc_perfil").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_perfil").Value = dc_perfil

            cmd.Parameters.Add("@dg_perfil", SqlDbType.VarChar, 80)
            cmd.Parameters("@dg_perfil").Direction = ParameterDirection.Input
            cmd.Parameters("@dg_perfil").Value = dg_perfil

            cmd.Parameters.Add("@dm_vigente", SqlDbType.Char, 1)
            cmd.Parameters("@dm_vigente").Direction = ParameterDirection.Input
            cmd.Parameters("@dm_vigente").Value = dm_vigente

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            Insertar_perfil = cmd.Parameters("ReturnValue").Value

            If Insertar_perfil <> 0 Then
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function Eliminar_perfil_transaccion(ByVal dc_perfil As Integer, ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_d_workflow_perfil_transaccion"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_perfil", SqlDbType.Int)
            cmd.Parameters("@dc_perfil").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_perfil").Value = dc_perfil

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            Eliminar_perfil_transaccion = cmd.Parameters("ReturnValue").Value

            If Eliminar_perfil_transaccion <> 0 Then
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function Insertar_perfil_transaccion(ByVal dc_perfil As Integer, ByVal dc_transaccion As Integer, ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_i_workflow_perfil_transaccion"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_perfil", SqlDbType.Int)
            cmd.Parameters("@dc_perfil").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_perfil").Value = dc_perfil

            cmd.Parameters.Add("@dc_transaccion", SqlDbType.SmallInt)
            cmd.Parameters("@dc_transaccion").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_transaccion").Value = dc_transaccion

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            Insertar_perfil_transaccion = cmd.Parameters("ReturnValue").Value

            If Insertar_perfil_transaccion <> 0 Then
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function Eliminar_perfil(ByVal dc_perfil As Integer, ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_d_workflow_perfil"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_perfil", SqlDbType.Int)
            cmd.Parameters("@dc_perfil").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_perfil").Value = dc_perfil

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            Eliminar_perfil = cmd.Parameters("ReturnValue").Value

            If Eliminar_perfil <> 0 Then
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function ConsultaPerfilesDisponibles(ByVal dc_usuario As String, ByRef obj_ds As DataSet, ByRef dg_resultado As String) As Long
        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_c_workflow_perfil_disponible"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            Dim obj_da As SqlDataAdapter = Nothing

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_usuario", SqlDbType.VarChar, 20)
            cmd.Parameters("@dc_usuario").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_usuario").Value = dc_usuario

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""
            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()

            obj_da = New SqlDataAdapter(cmd)
            obj_ds = New DataSet()
            obj_da.Fill(obj_ds)

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function ConsultaPerfilesAsociados(ByVal dc_usuario As String, ByRef obj_ds As DataSet, ByRef dg_resultado As String) As Long
        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_c_workflow_perfil_asociado"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            Dim obj_da As SqlDataAdapter = Nothing

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_usuario", SqlDbType.VarChar, 20)
            cmd.Parameters("@dc_usuario").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_usuario").Value = dc_usuario

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""
            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()

            obj_da = New SqlDataAdapter(cmd)
            obj_ds = New DataSet()
            obj_da.Fill(obj_ds)

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function Insertar_perfil_Usuario(ByVal dc_perfil As Integer, ByVal dc_usuario As String, ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_i_workflow_perfil_asociado"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_perfil", SqlDbType.Int)
            cmd.Parameters("@dc_perfil").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_perfil").Value = dc_perfil

            cmd.Parameters.Add("@dc_usuario", SqlDbType.VarChar, 20)
            cmd.Parameters("@dc_usuario").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_usuario").Value = dc_usuario

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            Insertar_perfil_Usuario = cmd.Parameters("ReturnValue").Value

            If Insertar_perfil_Usuario <> 0 Then
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

    Public Function Elimina_perfil_Usuario(ByVal dc_perfil As Integer, ByVal dc_usuario As String, ByRef dg_resultado As String) As Long

        Dim dg_conexion_desencriptada As String = ""
        dg_conexion_desencriptada = Encryptar.DecryptString("", dg_conexion, ModSeguridad.C_dg_clave, Security.Cryptography.CipherMode.ECB)
        Dim conn As New SqlConnection(dg_conexion_desencriptada)
        Try
            Dim dg_procedimiento As String = "sp_d_workflow_perfil_asociado"
            Dim cmd As New SqlCommand(dg_procedimiento, conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@dc_perfil", SqlDbType.Int)
            cmd.Parameters("@dc_perfil").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_perfil").Value = dc_perfil

            cmd.Parameters.Add("@dc_usuario", SqlDbType.VarChar, 20)
            cmd.Parameters("@dc_usuario").Direction = ParameterDirection.Input
            cmd.Parameters("@dc_usuario").Value = dc_usuario

            cmd.Parameters.Add("@dg_resultado", SqlDbType.VarChar, 255)
            cmd.Parameters("@dg_resultado").Direction = ParameterDirection.Output
            cmd.Parameters("@dg_resultado").Value = ""

            cmd.Parameters.Add("ReturnValue", SqlDbType.Int)
            cmd.Parameters("ReturnValue").Direction = ParameterDirection.ReturnValue
            cmd.Parameters("ReturnValue").Value = 0

            cmd.CommandTimeout = timesql_servidor
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            Elimina_perfil_Usuario = cmd.Parameters("ReturnValue").Value

            If Elimina_perfil_Usuario <> 0 Then
                dg_resultado = cmd.Parameters("@dg_resultado").Value
            End If

        Catch ex As Exception
            dg_resultado = Err.Description
            Return 2300
        Finally
            conn.Close()
            conn.Dispose()
        End Try

    End Function

End Class

